package com.share.poi.excel;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.beans.BeanInfo;
import java.beans.IntrospectionException;
import java.beans.Introspector;
import java.beans.PropertyDescriptor;
import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.util.*;

/**
 * author:caifan
 * date:2019/6/25
 */
public class ExcelUtils {
    public static final String Y_M_D_H_M_S = "yyyy-MM-dd HH:mm:ss";

    public static <T> void createExcel(JSONArray titleArray, List<T> dataList, String fileName) throws Exception {
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet();

        OutputStream os = new FileOutputStream(new File(fileName));
        createTitle(titleArray, workbook, sheet);
        List<Map<Integer, Object>> mergeData = mergeData(titleArray, dataList);
        fillSheet(workbook, sheet, mergeData);

        workbook.write(os);
        os.close();
        workbook.close();
    }

    public static void fillSheet(Workbook workbook, Sheet sheet, List<Map<Integer, Object>> dataMapList) {
        sheet.setDefaultColumnWidth(27);
        int rowIndex = 1;
        CellStyle cellStyle = createCellStyle(workbook, false);
        CellStyle dateCellStyle = createCellStyle(workbook, true);
        for (Map<Integer, Object> dataMap : dataMapList) {
            Row row = sheet.createRow(rowIndex);
            for (Map.Entry<Integer, Object> kv : dataMap.entrySet()) {
                Cell cell = row.createCell(kv.getKey());
                Object value = kv.getValue();
                if (value instanceof Date) {
                    cell.setCellValue((Date)value);
                    cell.setCellStyle(dateCellStyle);
                } else {
                    cell.setCellValue(String.valueOf(value));
                    cell.setCellStyle(cellStyle);
                }
            }
            rowIndex++;
        }
    }

    /*
     * JsonArray 存 列名 name_en name_cn 中 英 列位置index  List<T> 字段名及值 前两者将 位置和值合并成 list<map>
     */
    public static <T> List<Map<Integer, Object>> mergeData(JSONArray columnArray, List<T> dataList) {
        if (CollectionUtils.isEmpty(dataList)) {
            return Collections.emptyList();
        }
        Map<String, Integer> columnMap = new HashMap<>();
        for (Object obj : columnArray) {
            JSONObject json = (JSONObject) obj;
            columnMap.put(json.getString("name_en"), json.getInteger("index"));
        }

        List<Map<Integer, Object>> dataMapList = new ArrayList<>();
        for (T obj : dataList) {
            try {
                BeanInfo beanInfo = Introspector.getBeanInfo(obj.getClass());
                PropertyDescriptor[] propertyDescriptors = beanInfo.getPropertyDescriptors();
                Map<Integer, Object> dataMap = new HashMap<>();
                for (PropertyDescriptor propertyDescriptor : propertyDescriptors) {
                    /*
                     * 需要导出的列
                     */
                    String attributeName = propertyDescriptor.getName();
                    Method readMethod = propertyDescriptor.getReadMethod();
                    Object value = readMethod.invoke(obj);
                    if (null != columnMap.get(attributeName)) {
                        dataMap.put(columnMap.get(attributeName), value);
                    }
                }
                if (dataMap.size() > 0) {
                    dataMapList.add(dataMap);
                }
            } catch (IntrospectionException e) {
                e.printStackTrace();
            } catch (IllegalAccessException e) {
                e.printStackTrace();
            } catch (InvocationTargetException e) {
                e.printStackTrace();
            }
        }
        return dataMapList;
    }

    public static void createTitle(JSONArray titleArray, Workbook workbook, Sheet sheet) {
        if (null == titleArray) {
            return;
        }
        CellStyle cellStyle = createCellStyle(workbook, false);
        Row row = sheet.createRow(0);
        for (Object obj : titleArray) {
            JSONObject json = (JSONObject)obj;
            Cell cell = row.createCell(json.getInteger("index"));
            cell.setCellValue(json.getString("name_cn"));
            cell.setCellStyle(cellStyle);
        }
    }

    /**
     * 创建cell样式
     * @param workbook
     * @param dateFlag
     * @return
     */
    private static CellStyle createCellStyle(Workbook workbook, Boolean dateFlag) {
        CreationHelper creationHelper = workbook.getCreationHelper();
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        if (dateFlag) {
            cellStyle.setDataFormat(creationHelper.createDataFormat().getFormat(Y_M_D_H_M_S));
        }
        Font font = workbook.createFont();
        font.setFontHeightInPoints((short)16);
        cellStyle.setFont(font);
        return cellStyle;
    }

    /**
     *
     * @param outPath
     * @throws Exception
     */
    public static void createExcel(String outPath) throws Exception {
        Workbook wb = new XSSFWorkbook();
        try (OutputStream os = new FileOutputStream(outPath)) {
            Sheet sheet = wb.createSheet("test");
            Row row = sheet.createRow(1);
            Cell cell = row.createCell(1);
            cell.setCellValue("this is merging cell");
            /**
             * 合并行列 参数含义 起始行 结束行 起始列 结束列 0表示第1行or列
             */
            sheet.addMergedRegion(new CellRangeAddress(1, 1, 1,2));
            wb.write(os);
            wb.close();
        }

    }



    public static void main(String[] args) throws Exception {
        createExcel("D:/test/abc.xlsx");
    }
}
